/**
 * Company : Thai Informatic System Co., Ltd.
 * Created By : Chatree Siangklom
 * Created On : Apr 3, 2013  12:05:08 PM 
 * e-Mail : dr_chatree@hotmail.com
 * Mobile Phone : (+66) 087-921-5115
 * Package Name : promis.api.excel.write
 * File Name : ExcelInsertIntoTableServices.java
 * TODO
 */
package promis.api.excel.write;

import java.io.File;
import java.sql.Connection;
import java.sql.Statement;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import promis.api.oracle.TableServices;
import promis.db.connection.dbHelper;
import promis.utility.CommonService;
import promis.utility.Utilitys;

/**
 * @author chatree
 *
 */
public class ExcelInsertIntoTableServices {
	
	private String inputFile = "";
	private String owner = "SYNCHRO";
	public ExcelInsertIntoTableServices(String inputFile){
		this.inputFile = inputFile;
	}
		
	public void InsertIntoTableFromExcel(String owner, String table_name, int sheet_num) throws Exception {
		File inputWorkbook = new File(inputFile);
		Connection conn = new dbHelper().getConn();
		conn.setAutoCommit(true);
		
		if(CommonService.IsNull(owner))
			owner = this.owner;
		
		Workbook w;
		try{			
			w = Workbook.getWorkbook(inputWorkbook);
			// Get the first sheet
			
			//Sheet sheet = w.getSheet(0);
			Sheet sheet = w.getSheet(sheet_num);
			// Loop over first 10 column and lines
			
			if(!new TableServices().TableExists(owner, table_name))
				throw new Exception("Exception :: OWNER="+ owner +", "+ table_name +"= not Exists");
			
			StringBuffer _strQuery = new StringBuffer();			
			_strQuery.append(" INSERT INTO  "+ owner +"."+ table_name +" ( ");
			_strQuery.append(" FIELD_ID ");
						
			if(sheet.getRows() > 0)
			{
				int column_int = 0;
				int iRowHeader = 0; //Row Hrader
					for (int j = 0; j < sheet.getColumns(); j++) {
						Cell cell = sheet.getCell(j, iRowHeader);
						String column_header_name = CommonService.IsNullRString(cell.getContents()).replace(" ", "");
						if(CommonService.IsNull(column_header_name)){
							column_int++;
							_strQuery.append(" ,FIELD_"+ Utilitys.leftPad((column_int)+"", 3, "0") +" ");
						}else{
							_strQuery.append(" ,"+ column_header_name +" ");
						}
					}
					
					_strQuery.append(", ISACTIVE, EXCEL_CREATED_ON, EXCEL_UPDATED_ON ");
					_strQuery.append(" ) VALUES ( ");
									
					
				//>>>INSERT
				int irow = 0;
				int irow1 = 0;
				Statement stmt = conn.createStatement();
				for(int i = 1; i < sheet.getRows(); i++){
					irow1++;
					StringBuilder seq = new StringBuilder();
					seq.append(_strQuery.toString());
					seq.append(" "+ i +" ");
					for (int j = 0; j < sheet.getColumns(); j++) {
						Cell cell = sheet.getCell(j, i);
						String cellValue = CommonService.IsNullRNull(CommonService.IsNullRString(cell.getContents()).replace(";", "").replace("'", ""));
						seq.append(", "+ Utilitys.strSql(cellValue));
					}
					seq.append(" , 'Y', CURRENT_DATE, NULL) ");
					System.out.println(irow1 + "//" + irow + " : " + seq + "\n");
					
					stmt.executeUpdate(seq.toString());
					stmt.close();
					stmt = null;
					stmt = conn.createStatement();
					
					/*
					irow++;
					stmt.addBatch(seq.toString());
					if(irow == 1)						
					{
						stmt.executeBatch();
						stmt.close();
						stmt = null;
						stmt = conn.createStatement();
						System.err.println("conn.createStatement() ???");
						irow = 0;
					}
					*/
				}
			}
			
			conn.close();
			
		}catch(Exception e){
			e.printStackTrace();
			throw e;
		}finally{
			if(!conn.isClosed())
				conn.close();
			
			conn = null;
		}
	}
	

}
